IF OBJECT_ID('ssf.ptx_get_task_group_execution_report') IS NOT NULL
	DROP FUNCTION ssf.ptx_get_task_group_execution_report
GO

CREATE FUNCTION ssf.ptx_get_task_group_execution_report (
	@task_group_id INT
)
RETURNS XML
AS
BEGIN
	RETURN (
		SELECT dependency_level AS '@dependency_level'
			, task_id
			, task_database_name
			, task_procedure_name
			, task_parameter_list
			, priority
			, (
				SELECT task_id AS 'task_id'
				FROM ssf.ptx_get_active_task_parents(p.task_id)
				FOR XML PATH(''), ROOT('ptx_task_dependency'), ELEMENTS, TYPE
			)
			, (
				SELECT purpose AS 'purpose'
				FROM ssf.dpnd_get_object_summary(p.task_procedure_name)
				FOR XML PATH(''), ROOT('dpnd_object_summary'), ELEMENTS, TYPE
			)
			, (
				SELECT comment_sequence AS 'comment_sequence'
					, code_comment AS 'code_comment'
				FROM ssf.dpnd_get_object_comment(p.task_procedure_name)
				ORDER BY comment_sequence
				FOR XML PATH(''), ROOT('dpnd_object_comment'), ELEMENTS, TYPE
			)
		FROM ssf.ptx_get_task_group_execution_plan(@task_group_id) p
		ORDER BY dependency_level
			, priority
			, task_id
		FOR XML PATH('ptx_task'), ROOT('ptx_task_group'), ELEMENTS, TYPE
	)
END
GO
